﻿using System;
using System.Collections.Generic;
using System.Text;
using SQLServerHealthCheck.Model;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Linq;
using System.Collections;
using System.IO;

namespace SQLServerHealthCheck.SQLServer
{
   public static  class PerformanceCheck
    {

        public static List<Exception> errors;
        public static ArrayList arrPerformanceCheck = new ArrayList();
        public static List<Exception> GeneratePerformanceOverview(List<String> SQLServerInstances)
        {
            try
            {
                errors = new List<Exception>();

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringTemplate"].ToString());

                dc.ClearAll();

                List<Server> servers = new List<Server>();

                //delete the Installation Check Log file if it exists
                //string InstallationCheckLogfile = Application.StartupPath + "\\SQLServerHealthCheck_InstallationCheckLog.txt";

                //if (File.Exists(InstallationCheckLogfile))
                //{
                //    File.Delete(InstallationCheckLogfile);
                //}

                ////delete the Database Check Log file if it exists
                //string DatabaseCheckLogfile = Application.StartupPath + "\\SQLServerHealthCheck_DatabaseCheckLog.txt";

                //if (File.Exists(DatabaseCheckLogfile))
                //{
                //    File.Delete(DatabaseCheckLogfile);
                //}

                ////delete the Performance Check Log file if it exists
                //string PerformanceCheckFile = Application.StartupPath + "\\SQLServerHealthCheck_PerformanceCheckLog.txt";

                //if (File.Exists(PerformanceCheckFile))
                //{
                //    File.Delete(PerformanceCheckFile);
                //}

                InstallationCheck.arrInstallationCheck.Clear();
                DatabasesCheck.arrDatabaseCheck.Clear();
                arrPerformanceCheck.Clear();

                InstallationCheck.FillServersAndSQLServerServices(SQLServerInstances, dc, servers);
                InstallationCheck.FillServerDrives(dc, servers);
                InstallationCheck.FillServices(dc, servers);
                DatabasesCheck.FillDatabasesAndFiles(dc, servers);
                PerformanceCheck.FillPerformanceData(SQLServerInstances, dc, servers);
            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return errors;



        }


        private static void FillPerformanceData(List<String> SQLServerInstances, HealthCheckDataContext dc, List<Server> servers)
        {
          
                
           
       
            foreach (Server server in servers)
            {
                foreach (Service service in server.Services)
                {
                    foreach (Database database  in service.Databases)
                    {
                        if ((database.DatabaseName.Trim() != "master") && (database.DatabaseName.Trim() != "model") && (database.DatabaseName.Trim() != "distribution") && (database.DatabaseName.Trim() != "msdb") && (database.DatabaseName.Trim() != "tempdb") && (database.DatabaseName.Trim() != "distribution"))
                        {
                           
                                if (!database.DatabaseName.Contains("ReportServer"))
                                {
                                    FillDatabaseTableDetails(dc, database, service);
                                }
                           
                        }
                    }
                }

            }

        }

        private static void FillDatabaseTableDetails(HealthCheckDataContext dc, Database database,Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillDatabaseTableDetails: ", " FillDatabaseTableDetails method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (InstallationCheck.IsSQLServerAccessible(@"Server=" + service.InstanceName.ToString() + ";database=master;Integrated Security=True"))
                {


                    using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName.ToString() + ";Database=master;Integrated Security=True"))
                    {
                        cn.Open();
                        String sql = @"	


Declare @Command Varchar(8000)
Declare @a Varchar(8000),@Str Varchar(8000),@id int
Declare @DBNVar1 Varchar(8000),
		@ObjIDVar1 Varchar(8000), 
		@ObjNameVar1 Varchar(8000),
		@IndIDVar1 Varchar(8000),
		@IndNAMEVar1 Varchar(8000),
		@IndexTypeVar Varchar(8000),
@last_updated varchar(8000),
		@RecommendationVar Varchar(8000) 



Declare @Str1 Varchar(8000)

Create Table ##IndNAME 
(
	IndNAME Varchar(8000)
)

Set @Str='' 
Create Table ##SQL_FragINFO 
(
		DatabaseName Varchar(8000),
	ObjectID Varchar(8000),
	ObjectName Varchar(8000),
	Index_ID Varchar(8000),
	Index_Type_DESC Varchar(8000),
[IndexSize(MB)] varchar(8000),
last_updated datetime,
	Avg_Fragmentation_In_Percent Varchar(8000)
)

Create Table ##SQL_FragLIST 
(
		DatabaseName Varchar(8000),
	ObjectID Varchar(8000),
	ObjectName Varchar(8000),
	Index_ID Varchar(8000),
	indexName varchar(8000),
	Index_Type_DESC Varchar(8000),
[IndexSize(MB)] varchar(8000),
last_updated datetime,
	Avg_Fragmentation_In_Percent Varchar(8000),
	Recommendation varchar(8000)
)

 
declare @dbname varchar(8000)



Set @dbname='" + database.DatabaseName.ToString() + "'";

                        sql = sql + @" 
   Select @Str='Use ['+Ltrim(@dbname)+'] 

 select 	DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,
		CONVERT(DECIMAL(16,1),(sum(avg_record_size_in_bytes * record_count)/ (1024.0 * 1024)))
		as ''IndexSize(MB)''
		,last_updated,Avg_Fragmentation_In_Percent
		from( 
SELECT Distinct DB_Name(Database_id) as ''DatabaseName'',
	Object_ID as ''ObjectId'',
	Object_Name(Object_id) as ''ObjectName'',
	Index_ID as ''IndexId'',
	Index_Type_Desc as ''Index_Description'',
	avg_record_size_in_bytes , record_count,
 STATS_DATE(object_id,index_id) as ''last_updated'',
	Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) as ''Avg_Fragmentation_In_Percent''
		FROM sys.dm_db_index_physical_stats (DB_ID('''+@dbname +'''), NULL, NULL, NULL, ''detailed'') 
		Where Object_id is not null and Avg_Fragmentation_In_Percent <> 0
) T
	 group by 	DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,last_updated,Avg_Fragmentation_In_Percent
'
		
	
	Insert into ##SQL_FragINFO Exec (@Str)
	
insert  Into ##SQL_FragLIST

Select DatabaseName,
	ObjectID,
	ObjectName,
	Index_ID,
	'******************NOT-AVAILABLE*********************' as IndexNAME,
	Index_Type_Desc,
[IndexSize(MB)],
last_updated,
	Avg_Fragmentation_In_Percent  as 'Avg_Fragmentation_In_Percent',
	'Recommendation'= 
	Case
		When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) > 30.000 and Index_Type_Desc ='NONCLUSTERED INDEX') then 'REBUILD THIS INDEX'
		When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) <= 30.000 and Index_Type_Desc ='NONCLUSTERED INDEX') then 'DEFRAG THIS INDEX'
		When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) > 30.000 and Index_Type_Desc ='CLUSTERED INDEX') then 'REBUILD THIS INDEX'
		When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) <= 30.000 and Index_Type_Desc ='CLUSTERED INDEX') then 'DEFRAG THIS INDEX'
		When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) > 10.000 and Index_Type_Desc ='HEAP') then 'CREATE A CLUSTERED INDEX THEN DROP IT'
		When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) <= 10.000 and Index_Type_Desc ='HEAP') then 'NO ACTION REQUIRED'
	End 
	 from ##SQL_FragINFO order by 1,2 desc

Declare CheckFrag Cursor For 
	Select 
		DatabaseName,
		ObjectID,
		ObjectName,
		Index_ID,
		Index_Type_Desc, 
last_updated,
		Recommendation 
	from ##SQL_FragLIST 
	Open CheckFrag
	Fetch Next from CheckFrag Into 
		@DBNVar1,
		@ObjIDVar1,
		@ObjNameVar1,
		@IndIDVar1,
		@IndexTypeVar,
@last_updated,
		@RecommendationVar    

	While (@@Fetch_Status=0)
	Begin
		Truncate Table ##IndName
		Exec('Insert into ##IndNAME Select Name from ['+@DBNVar1+']..SysIndeXes 
		where IndID = '+@IndIDVar1+' and ID='+@ObjIDVar1)
		
		Select @Str1=IndNAME from ##IndNAME

	Update ##SQL_FragLIST
		Set IndexNAME = ISNULL(@Str1,'NOT-AVAILABLE')
		Where ObjectID=@ObjIDVar1 and 
		DatabaseName=@DBNVar1 

	Fetch Next from CheckFrag into 
		@DBNVar1,
		@ObjIDVar1,
		@ObjNameVar1,
		@IndIDVar1,
		@IndexTypeVar,
@last_updated,
		@RecommendationVar    
End
Close CheckFrag
Deallocate CheckFrag

If (Select count(*) from ##SQL_FragLIST) >=1
Begin
Select DatabaseName,
		ObjectName,
		Index_ID,
		IndexNAME,
		Index_Type_DESC,
[IndexSize(MB)],
last_updated,
		Avg_Fragmentation_In_Percent, 
		Recommendation 
	from ##SQL_FragLIST
End

Drop Table ##IndNAME 
	Drop Table ##SQL_FragINFO
	Drop Table ##SQL_FragLIST
";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();


                        while (rd.Read())
                        {
                            DatabaseTableDetail dt = null;


                            if (database.DatabaseTableDetails.Count(d => d.TableName == rd[1].ToString()) == 0)
                            {

                                dt = new DatabaseTableDetail();

                                dt.TableName = rd[1].ToString();

                                database.DatabaseTableDetails.Add(dt);
                            }
                            else
                            {
                                dt = database.DatabaseTableDetails.First(d => d.TableName == rd[1].ToString());
                            }

                            IndexFragmentationDetail idf = new IndexFragmentationDetail();
                            idf.IndexName = rd[3].ToString();
                            idf.IndexType = rd[4].ToString();
                            idf.IndexSize = rd[5].ToString();
                            idf.Statistics_LastUpdatedDate = rd.IsDBNull(6) ? (DateTime?)null : rd.GetDateTime(6);
                            idf.Fragmentation = (rd[7].ToString().Trim());
                            idf.Recommendation = rd[8].ToString();

                            dt.IndexFragmentationDetails.Add(idf);
                        }


                        rd.Close();


                    }


                    dc.SubmitChanges(); 

                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillDatabaseTableDetails: ", " FillDatabaseTableDetails method in PerformanceCheck Completed for " + service.InstanceName.ToString());
                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrPerformanceCheck.Add(service.InstanceName.ToString());
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillDatabaseTableDetails: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }
            }


            catch (Exception ex)
            {


                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillDatabaseTableDetails: ", " Some error occured in Performance Check.Please check exception file for details");

            }

              

        }

       
    }
}
